from sqlalchemy import text
from sqlalchemy.orm import Session
from models import Person, FaceEmbedding
import uuid



class FaceDBService:
    def __init__(self, db: Session):
        self.db = db

    def create_person(self, name: str, metadata: dict = None):
        person = Person(name=name, person_metadata=metadata or {})
        self.db.add(person)
        self.db.commit()
        return person

    def add_face_embedding(self, person_id: str, embedding: list, image_path: str = None):
        face_embedding = FaceEmbedding(
            person_id=uuid.UUID(person_id),
            embedding=embedding,
            image_path=image_path,
            detection_metadata={}
        )
        self.db.add(face_embedding)
        self.db.commit()
        return face_embedding

    def search_face(self, query_embedding: list, threshold: float = 0.7, limit: int = 5):
        # 使用pgvector的相似度搜索
        sql = text("SELECT person_id, name, 1-(embedding <=> (:embedding)::vector) AS similarity FROM face_embeddings JOIN persons ON persons.id = face_embeddings.person_id WHERE 1-(embedding <=> (:embedding)::vector) > :threshold ORDER BY similarity DESC LIMIT :limit")
        results = self.db.execute(
            sql,
            {'embedding': query_embedding, 'threshold': threshold, 'limit': limit}
        )
        rows = results.fetchall()
        return [dict(row._mapping) for row in rows]